var express = require('express')
var router = express.Router()


// 导入db, 操作数据库
const { getAll, getById, exec } = require('../db/index')
router.get('/all', async (req, res) => {
  // 操作数据库
  let sql = `select * from categories`
  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '获取博客分类成功',
    result: {
      data: data
    }
  })
})


router.get("/", async (req, res) => {
  // 解析请求参数
  var { page, size } = req.query;
  var sql = `select count(*) as total from categories`;
  let { total } = await getById(sql); // { total: 9 }
  // 偏移量 = (page -1) * size
  var offset = (page - 1) * size;
  // 操作数据库
  sql = `select id, alias from categories order by id desc limit ${offset}, ${size}`;
  var data = await getAll(sql);
  if (data.length == 0) {
    page = page - 1;
    offset = (page - 1) * size;
    sql = `select id, alias from categories order by id desc limit ${offset}, ${size}`;
    data = await getAll(sql);
  }
  res.send({
    code: 0,
    message: "获取所有分类成功",
    result: {
      total,
      data,
    },
    page: page,
  });
});

router.get("/:id", async (req, res) => {
  const { id } = req.params;

  let sql = `select * from categories where id=${id}`;
  const data = await getById(sql);

  res.send({
    code: 0,
    message: "获取单个分类成功",
    result: data,
  });
});

/* router.get('/', async (req, res) => {
  try {
    const data = await getAll('select * from categories')
    res.send({
      code: 0,
      message: '获取所有分类成功',
      result: data
    })
  } catch (e) {
    console.log(e)
  }
})
 */
router.get('/search/category', async (req, res) => {
  // 操作数据库
  const { page, size, searchMsg } = req.query
  let offset = (page - 1) * size
  let sql = `SELECT * from categories WHERE alias LIKE '%${searchMsg}%' limit ${offset},${size}`
  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '查询分类成功',
    result: {
      data: data
    }
  })
})
router.post("/", async (req, res) => {
  // 1.解析请求数据
  const { alias } = req.body;
  let sql = `select * from categories where alias ='${alias}' `;
  const result = await getAll(sql);
  if (result.length >= 1) {
    res.send({
      code: 1,
      message: "该博客分类已存在，请输入新的分类",
    });
    return;
  }
  // 2.编写sql语句
  sql = `insert into categories (alias) values ('${alias}')`;
  // 3.执行sql语句
  const data = await exec(sql);
  res.send({
    code: 0,
    message: "添加分类成功",
    // result: {
    //   id: data.insertId,
    //   alias: alias,
    // },
  });
});

router.put("/:id", async (req, res) => {
  const { id } = req.params;
  const { alias } = req.body;
  var sql = `select * from categories where alias = '${alias}' `;
  const data = await getAll(sql);
  if (data.length >= 1) {
    res.send({
      code: 1,
      message: "该分类已存在，请输入新的值",
    });
    return;
  }
  sql = `update categories set alias ="${alias}" where id=${id}`;
  await exec(sql);
  res.send({
    code: 0,
    message: "更新分类成功",
  });
});

router.delete("/:id", async (req, res) => {
  const { id } = req.params;
  // 编写sql
  let sql = `delete from categories where id=${id}`;

  await exec(sql);

  res.send({
    code: 0,
    message: "删除成功",
    result: "",
  });
});

// 四. 导出router对象

module.exports = router;
